﻿using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
    public class InvestRepository : BaseRepository
    {
        public PagedList<T_Investment_EX> QueryInvestList(InvestSearch search, PageView view)
        {
            string cols = @" i.[Id]
                          ,p.[ProjectName]
                          ,m.[Name] AS UserName
                          ,i.[TenderAmt]
                          ,i.[CreateTime]
                          ,i.[CreateUser]
                          ,i.[Status]";
            string sqlWhere = "";
            sqlWhere += " and i.[Status]=0 " + "";//添加一个判断条件，成功充值后的人，状态是0.
            if (search.MemberId > 0)
            {
                sqlWhere += " AND i.[MemberId]=" + search.MemberId + "";
            }
            if (search.ProjectId > 0)
            {
                sqlWhere += " AND i.[ProjectId] =" + search.ProjectId + "";
            }
            return base.PageGet<T_Investment_EX>(view, cols,
                @" [T_Investment] i LEFT JOIN [T_Member] m ON i.MemberId=m.ID  LEFT JOIN [T_Project] p ON i.ProjectId = p.ID "
                , sqlWhere
                , " i.[Id] DESC ",
                "");
        }

        public List<T_Investment_EX> QueryInvestList(int pageindex, int memberId)
        {
            string sql = @"SELECT 
                        t.* from
                        (SELECT ROW_NUMBER() over (order by i.id desc) as row,i.[Id]
                          ,p.[ProjectName]
                          ,m.[Name] AS UserName
                          ,i.[TenderAmt]
                          ,CONVERT(char(16),i.[CreateTime],120) as CreateDateStr
                          ,i.[CreateUser]
                          ,i.[Status] FROM [T_Investment] i LEFT JOIN [T_Member] m ON i.MemberId=m.ID  LEFT JOIN [T_Project] p ON i.ProjectId = p.ID WHERE i.[MemberId]=@MemberId ) 
                            
t where t.row between 10*(" + pageindex + "-1)+1 and  10*(" + pageindex + "-1)+10 ";
            return base.Query<T_Investment_EX>(sql, new { MemberId = memberId });
        }

        /// <summary>
        /// 个人投资总数
        /// </summary>
        /// <param name="memberId"></param>
        /// <returns></returns>
        public int QueryInvestCount(int memberId)
        {
            string sql = @" SELECT COUNT(i.[Id]) AS [COUNT]
                         FROM [T_Investment] i LEFT JOIN [T_Member] m ON i.MemberId=m.ID  LEFT JOIN [T_Project] p ON i.ProjectId = p.ID WHERE i.[MemberId]=@MemberId";

            return base.Get<int>(sql, new { MemberId = memberId });
        }

        public T_Investment_EX GetInvest(int? Id)
        {
            string sql = @" SELECT i.[Id]
                          ,i.[ProjectId]
                          ,p.[ProjectName]
                          ,m.[Name] AS UserName
                          ,i.[TenderAmt]
                          ,i.[CreateTime]
                          ,i.[CreateUser]
                          ,i.[Status]
                        ,i.AnnualisedRates
                        ,i.AddAnnualisedRates
                        ,i.AddMAnnualisedRates
                      FROM [T_Investment] i LEFT JOIN [U_Member] m ON i.MemberId=m.ID  LEFT JOIN [T_Project] p ON i.ProjectId = p.ID
                       WHERE i.[Id]=@ID";
            return base.Get<T_Investment_EX>(sql, new { ID = Id });
        }
        /// <summary>
        /// 显示最新投标的用户信息
        /// </summary>
        /// <returns></returns>
        public List<T_Investment_EX> GetInvestment()
        {
            string sql = @" SELECT TOP(30) Tment.[Id]
                                  ,[ProjectId]
                                  ,SUBSTRING(Tber.[Name],1,3) AS UserName
                                  ,Tber.[Address] AS Address
                                  ,[TenderAmt]
                                  ,[CreateTime]
                                  ,[CreateUser]
                                  ,Tment.[Status]
                            FROM T_Investment Tment
                            LEFT JOIN T_Member Tber
                            ON Tment.MemberId=Tber.ID
                            ORDER BY [CreateTime] DESC";
            return base.Query<T_Investment_EX>(sql, null);
        }

        /// <summary>
        /// 用户投标排行
        /// </summary>
        /// <returns></returns>
        public List<T_Investment_EX> GetInverstmentRanking()
        {
            string sql = @" SELECT TOP 7
                          SUBSTRING(mem.Name,1,3) AS UserName
                          ,SUM([TenderAmt]) AS TenderAmt
                          FROM [T_Investment] ment
                          left join [T_Member] mem on ment.MemberId=mem.ID
                          GROUP BY Name ORDER BY TenderAmt DESC";
            return base.Query<T_Investment_EX>(sql, null);
        }

        //用户投资还款计划
        public List<T_RepaymentPlan_EX> GetRepList(int? tid, int mid)
        {
            string sql = @" SELECT A.[ID]
      ,A.[RepaymentPlanNO]
    ,ISNULL(CONVERT(char,CONVERT(char(10),A.[RepaymentPlanDate],120)),'--待确定--') AS [RepaymentPlanDateStr]
      ,A.[CurrentPrincipal]
      ,A.[RemainingPrincipal]
      ,A.[Status]
      ,A.[ProjectID]
      ,B.TenderAmt
      ,convert(decimal(18,2),(B.TenderAmt/C.TotalLoan)*A.[CurrentPrincipal]) RepayAmt
  FROM [T_RepaymentPlan] A 
  left join T_Investment B
  on A.ProjectID=B.ProjectId 
    left join T_Project C on A.ProjectID=c.ID 
  where b.Id=@TID AND b.MemberId=@MID";
            return base.Query<T_RepaymentPlan_EX>(sql, new { TID = tid, MID = mid });
        }

        /// <summary>
        /// 显示投标的用户信息
        /// </summary>
        /// <returns></returns>
        public PagedList<T_Investment_EX> GetInvestmentList(int? id, int? projectid, ProjectSearch search, PageView view)
        {
            string cols = @" a.[Id]
                          ,a.[ProjectId]
                          ,a.[MemberId]
                          ,a.[TenderAmt]
                          ,a.[CreateTime]
                          ,a.[CreateUser]
                          ,a.[Status]
                          ,a.[AnnualisedRates]
                          ,a.[AddAnnualisedRates]
                          ,a.[AddMAnnualisedRates]
                          ,b.Name as Name
                          ,c.ProjectName
                          ,case 
                          when c.DurationMonthOrDay = 1 and d.RemainingPrincipal <> 0 then (a.TenderAmt / 12) * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) 
                          when c.DurationMonthOrDay = 1 and d.RemainingPrincipal = 0 then (a.TenderAmt / 12) * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) + a.TenderAmt
                          when c.DurationMonthOrDay = 2 and d.RemainingPrincipal = 0 then  a.TenderAmt / datediff(day,rtrim(YEAR(TenderTime))+'0101',rtrim(YEAR(TenderTime)+1)+'0101') * c.ProjectDuration * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) + a.TenderAmt
                          else a.TenderAmt / datediff(day,rtrim(YEAR(TenderTime))+'0101',rtrim(YEAR(TenderTime)+1)+'0101') * c.ProjectDuration * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) end AS Profit ";
            string sqlWhere = " and a.Status=1 ";
            //if (search.MemberId > 0)
            //{
            //    sqlWhere += " AND i.[MemberId]=" + search.MemberId + "";
            //}
            if (projectid > 0)
            {
                sqlWhere += " AND a.[ProjectId] =" + projectid + " and d.ID= " + id + " ";
            }
            return base.PageGet<T_Investment_EX>(view, cols,
                @" [T_Investment] a left join [U_Member] b on a.[MemberId]=b.ID left join [T_Project] c on a.ProjectId = c.ID left join T_RepaymentPlan d on a.ProjectId=d.ProjectID"
                , sqlWhere
                , " a.CreateTime DESC ",
                "");
        }

        //还本金
        public int SaveReptPrincipal(int? projectid, string name, int? id, double? fee)
        {
            string sql = @" INSERT INTO [U_FundsFlow]([MemberId]
                        ,[InCome]
                        ,[CreateTime]
                        ,[Type]
                        ,[ProjectId]
                        ,[ParentId],[Status])

                        select 
                        a.[MemberId]
                        ,a.[TenderAmt]
                        ,GETDATE()
                        ,7
                        ,a.[ProjectId]
                        ,a.[Id]
                        ,1
                        from
                        [T_Investment] a left join [U_Member] b on a.[MemberId]=b.ID left join [T_Project] c on a.ProjectId = c.ID 
                        where
                        a.[ProjectId] =@Projectid ";
              return base.Excute(sql, new {Projectid = projectid});
        }

        //把收益添加到U_FundsFlow表中
        public int SaveRept(int? projectid, string name, int? id, double fee)
        {
            if (fee>0.00)//表示有手续费
            {
                string sql = @"INSERT INTO [U_FundsFlow]([MemberId],[OutPay],[CreateTime],[Type],[ProjectId],[ParentId],[Status])
                                select a.[MemberId]
                                ,case 
                                when c.DurationMonthOrDay = 1 then (a.TenderAmt / 12) * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) * @Fee 
                                else a.TenderAmt / datediff(day,rtrim(YEAR(TenderTime))+'0101',rtrim(YEAR(TenderTime)+1)+'0101') * c.ProjectDuration * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) * @Fee end AS Profit 
                                ,GETDATE(),8,a.[ProjectId],a.[Id],1
                                from
                                [T_Investment] a left join [U_Member] b on a.[MemberId]=b.ID left join [T_Project] c on a.ProjectId = c.ID 
                                where a.[ProjectId] =@projectid  

                               INSERT INTO [U_FundsFlow]([MemberId],[InCome],[CreateTime],[Type],[ProjectId],[ParentId],[Status])
                                select a.[MemberId]
                                ,case 
                                when c.DurationMonthOrDay = 1 then (a.TenderAmt / 12) * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) * (1- @Fee ) 
                                else a.TenderAmt / datediff(day,rtrim(YEAR(TenderTime))+'0101',rtrim(YEAR(TenderTime)+1)+'0101') * c.ProjectDuration * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) * (1- @Fee ) end AS Profit 
                                ,GETDATE(),5,a.[ProjectId],a.[Id],1
                                from
                                [T_Investment] a left join [U_Member] b on a.[MemberId]=b.ID left join [T_Project] c on a.ProjectId = c.ID 
                                where a.[ProjectId] =@Projectid 
                            ";
                return base.Excute(sql, new { Projectid = projectid, Fee = fee });
            }
            else
            {
                string sql = @" INSERT INTO [U_FundsFlow]([MemberId],[InCome],[CreateTime],[Type],[ProjectId],[ParentId],[Status])
                                select 
                                a.[MemberId]
                                ,case 
                                when c.DurationMonthOrDay = 1 then (a.TenderAmt / 12) * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) 
                                else a.TenderAmt / datediff(day,rtrim(YEAR(TenderTime))+'0101',rtrim(YEAR(TenderTime)+1)+'0101') * c.ProjectDuration * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) end AS Profit 
                                ,GETDATE()
                                ,5
                                ,a.[ProjectId]
                                ,a.[Id]
                                ,1
                                from
                                [T_Investment] a left join [U_Member] b on a.[MemberId]=b.ID left join [T_Project] c on a.ProjectId = c.ID 
                                where
                                a.[ProjectId] =@Projectid ";
                return base.Excute(sql, new { Projectid = projectid, ID = id });
            }
            
            
        }

        /// <summary>
        /// 显示最新投标Id
        /// </summary>
        /// <returns></returns>
        public int GetLastId()
        {
            string sql = "select MAX(Id) from T_Investment";
            return base.Get<int>(sql, new { ProjectId = 0 });
        }

        public int GetIdByCreatTime(DateTime time)
        {
            string sql = "select Id from T_Investment where CreateTime=@CreateTime";
            return base.Get<int>(sql, new { CreateTime = time });
        }

        public int Update(string submitBidNo, DateTime time, int status)
        {
            string sql = "update T_Investment set CreateTime=@CreateTime ,Status=@Status where submitBidNo=@SubmitBidNo";
            return base.Excute(sql, new { CreateTime = time, Status = status, SubmitBidNo = submitBidNo });
        }

        //项目已投金额
        public decimal MoneyCount(int projectId)
        {
            string sql = "select SUM(TenderAmt) from T_Investment where ProjectId=@ProjectId";
            return base.Get<decimal>(sql, new { ProjectId = projectId });
        }

        //根据时间查询出投标的订单号
        public string GetBidNoByCreatTime(DateTime time)
        {
            string sql = "select SubmitBidNo from T_Investment where CreateTime=@CreateTime";
            return base.Get<string>(sql, new { CreateTime = time });
        }

        //根据标号查询出投资人的信息列表
        public List<T_Investment_EX> GetSftInvestment(int projectId)
        {
            string sql = @"select T_Member.SFTUserName as userName,sum(TenderAmt) as amount,0 as platformFee,1 as transferMode from T_Investment,T_Member 
where T_Investment.Status=0 and ProjectId=@ProjectId and T_Investment.MemberId=T_Member.ID group by T_Member.SFTUserName";
            return base.Query<T_Investment_EX>(sql, new { ProjectId = projectId });
        }

        //根据submitbidno查询出一个对象
        public T_Investment_EX GetInvestmentBySubmitBidNo(string submitBidNo)
        {
            string sql = @"SELECT [Id] ,[ProjectId],[MemberId] ,[TenderAmt],[CreateTime],[CreateUser],[Status],[AnnualisedRates],[AddAnnualisedRates]
                ,[AddMAnnualisedRates],[SubmitBidNo] FROM [T_Investment] where SubmitBidNo=@SubmitBidNo";
            return base.Get<T_Investment_EX>(sql, new { SubmitBidNo = submitBidNo });
        }

        //根据submitbidno查询出一个对象
        public T_Investment GetModelBySubmitBidNo(string submitBidNo)
        {
            string sql = @"select Id ,ProjectId, MemberId, TenderAmt, CreateTime, CreateUser, [Status], AnnualisedRates, AddAnnualisedRates, AddMAnnualisedRates, SubmitBidNo
                            from dbo.T_Investment 
                            where SubmitBidNo=@SubmitBidNo";
            return base.Get<T_Investment>(sql, new { SubmitBidNo = submitBidNo });
        }

        //查询出还款记录的详细值
        public List<T_Investment_EX> QueryInvestmentList(int projectid,int repaymentPlanId)
        {
//            string sql = @"select a.[Id],a.[ProjectId],a.[MemberId],a.[TenderAmt],a.[CreateTime],a.[CreateUser],a.[Status],a.[AnnualisedRates]
//                          ,a.[AddAnnualisedRates],a.[AddMAnnualisedRates],b.RealName,c.ProjectName
//                          ,case 
//                          when c.DurationMonthOrDay = 1 then (a.TenderAmt / 12) * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) 
//                          else a.TenderAmt / datediff(day,rtrim(YEAR(TenderTime))+'0101',rtrim(YEAR(TenderTime)+1)+'0101') * c.ProjectDuration * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) end AS Profit 
//                          from  [T_Investment] a left join [U_Member] b on a.[MemberId]=b.ID left join [T_Project] c on a.ProjectId = c.ID 
//                          where  a.[ProjectId] = @ProjectId ";
            string sql = @"select k.MemberId,sum(k.Profit) as Profit  from
                            (
	                            select a.[ProjectId],a.[MemberId],a.[TenderAmt]
      
                                  ,case 
                                  when c.DurationMonthOrDay = 1 and d.RemainingPrincipal <>0 then (a.TenderAmt / 12) * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) 
                                  when c.DurationMonthOrDay = 1 and d.RemainingPrincipal = 0 then (a.TenderAmt / 12) * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) + a.TenderAmt
                                  when c.DurationMonthOrDay = 2 and d.RemainingPrincipal = 0 then  a.TenderAmt / datediff(day,rtrim(YEAR(TenderTime))+'0101',rtrim(YEAR(TenderTime)+1)+'0101') * c.ProjectDuration * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) + a.TenderAmt
                                  else a.TenderAmt / datediff(day,rtrim(YEAR(TenderTime))+'0101',rtrim(YEAR(TenderTime)+1)+'0101') * c.ProjectDuration * (ISNULL(a.AnnualisedRates, 0) + ISNULL(a.AddAnnualisedRates, 0) + ISNULL(a.AddMAnnualisedRates, 0)) end AS Profit 
                                  from  [T_Investment] a left join [U_Member] b on a.[MemberId]=b.ID left join [T_Project] c on a.ProjectId = c.ID left join T_RepaymentPlan d on a.ProjectId=d.ProjectID
                                  where a.[Status]=1 and a.[ProjectId] = @ProjectId and d.ID=@RepaymentPlanId
                            )k
                            group by k.MemberId";
            return base.Query<T_Investment_EX>(sql, new {ProjectId = projectid, RepaymentPlanId = repaymentPlanId});
        }

        public PagedList<T_Investment> QueryInvestmentList(InvestSearch search, PageView view)
        {
            string cols = @" Id, ProjectId, MemberId, TenderAmt, CreateTime, CreateUser, Status, AnnualisedRates, AddAnnualisedRates, AddMAnnualisedRates, SubmitBidNo ";
            string sqlWhere = " and Status=1";
            if (search.ProjectId >0 )
            {
                sqlWhere += "AND ProjectId=" + search.ProjectId;
            }
            return base.PageGet<T_Investment>(view, cols, @" [T_Investment]", sqlWhere, " [Id] Desc", "");
        }

        public T_Investment GetModel(int id)
        {
            string sql = @"select Id, ProjectId, MemberId, TenderAmt, CreateTime, CreateUser, Status, AnnualisedRates, AddAnnualisedRates, AddMAnnualisedRates, SubmitBidNo
                            from dbo.T_Investment 
                            where Id=@Id";
            return base.Get<T_Investment>(sql, new { Id = id });
        }

        //将所有已投资的人的状态改为2，表示流标已退款
        public int LiuBiaoTuiKuan(int projectId)
        {
            string sql = "Update T_Investment set Status=2 where Status=1 and ProjectId=@ProjectId";
            return base.Excute(sql, new { ProjectId = projectId });
        }
    }
}
